package com.hy.dao;

import com.hy.bean.RespBean;
import com.hy.bean.SysDept;
import com.hy.utils.DBManager;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;

import java.sql.Connection;
import java.util.*;

/**
 *
 *
 * @Description: 部门
 * @Author: zhangzhengwei2006
 * @Date: 2024-01-31 20:43
 *
 *
 */
public class SysDeptDao {
    private QueryRunner queryRunner = new QueryRunner();

    // 查询level值
    public SysDept selectLeelById(Integer parentId) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select * from sys_dept where id = ?";
        SysDept level = queryRunner.query(conn, sql, new BeanHandler<>(SysDept.class), parentId);
        DbUtils.close(conn);
        return level;
    }

    // 查询全都部门返回List<Map<String, Object>>
    public List<Map<String, Object>> selectAllDept() throws Exception {
        Connection conn = DBManager.getConn();
        //        String sql = "select * from sys_dept";
        String sql = "select * from sys_dept where status = 1 order by seq";
        List<Map<String, Object>> query = queryRunner.query(conn, sql, new MapListHandler());
        DbUtils.close(conn);
        return query;
    }

    // 查询全都部门返回List<SysDept>
    public List<SysDept> selectAllDeptList() throws Exception {
        Connection conn = DBManager.getConn();
        //        String sql = "select * from sys_dept";
        String sql = "select * from sys_dept where status = 1 order by seq";
        List<SysDept> query = queryRunner.query(conn, sql, new BeanListHandler<>(SysDept.class));
        DbUtils.close(conn);
        return query;
    }


    //查询重复值
    public Boolean selectByName(String name) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select * from sys_dept where name = ? and status = 1";
        String name1 = queryRunner.query(conn, sql, new ScalarHandler<>("name"), name);
        if (name1 == null) {
            return true;
        }
        DbUtils.close(conn);
        return false;
    }

    //添加部门
    public int insertDept(SysDept dept) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "insert into sys_dept values(null,?,?,?,?,?,?,?,?,?)";
        int update = queryRunner.update(conn, sql, dept.getName(), dept.getLevel(), dept.getSeq(), dept.getRemark(), dept.getStatus(), dept.getParent_id(), dept.getOperator(), dept.getOperate_time(), dept.getOperate_ip());
        DbUtils.close(conn);
        return update;
    }

    // 根据父级查询所有子
    public List<SysDept> selectByParentId(Integer id) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select * from sys_dept where parent_id = ?";
        List<SysDept> query = queryRunner.query(conn, sql,new BeanListHandler<>(SysDept.class), id);
        DbUtils.close(conn);
        return query;
    }

    // 删除
    public Boolean delectByid(Integer id) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "update sys_dept set status = 2 where id = ?";
        int update = queryRunner.update(conn, sql, id);
        DbUtils.close(conn);
        return update > 0 ? true : false;
    }

    // 张老师
    public boolean countByNameAndParentId(Connection conn, Integer parentId, String name, Integer id) throws Exception {
        StringBuilder sb = new StringBuilder("select count(1) from sys_dept where name = ? ");

        List<Object> paramList = new ArrayList<>();

        if (name != null) {
            paramList.add(name);
        } else {
            paramList.add("");
        }

        if (parentId != null) {
            sb.append(" and parent_id = ? ");
            paramList.add(parentId);
        } else {
            sb.append(" and parent_id = 0 ");
        }

        if (id != null) {
            sb.append(" and id != ?");
            paramList.add(id);
        }

        // select count(1) from sys_dept where name = '运维部2' and parent_id = 1


        System.out.println(sb.toString());
        System.out.println(paramList.toString());
        /*
         ScalarHandler(int columnIndex) | ScalarHandler(String columnName) --
         返回结果集中第一行某一列的值
         */
        Long count = queryRunner.query(conn, sb.toString(), new ScalarHandler<Long>(1), paramList.toArray());

        System.out.println("count" + count);
        return count > 0 ? true : false;
    }

    public SysDept selectById(Connection conn, Integer id) throws Exception {
        //第3步:
        String sql = "select `id`,`name`,`level`,`seq`,`remark`,`status`,`parent_id`,`operator`, `operate_time`,`operate_ip` from sys_dept where id = ?";
        //第4步：执行SQL语句 并处理结构
        SysDept sysDept = queryRunner.query(conn, sql, new BeanHandler<SysDept>(SysDept.class), id);

        return sysDept;
    }

    public List<SysDept> selectChildDeptListByLevel(Connection conn, String level) throws Exception {
        String sql = "select `id`,`name`,`level`,`seq`,`remark`,`status`,`parent_id` as `parentId`,`operator`, `operate_time` as `operateTime`," + "`operate_ip` as `operateIp` from sys_dept where `level` like concat(?,'.%') order by `id`";

        List<SysDept> sysDeptList = queryRunner.query(conn, sql, new BeanListHandler<SysDept>(SysDept.class), level);

        return sysDeptList;
    }

    public boolean updateById(Connection conn, SysDept sysDept) throws Exception {
        String sql = "update sys_dept set name = ?,level =?,seq =? ,remark =?,parent_id=?,operator=?,operate_time=?,operate_ip = ? where id = ? ";

        int count = queryRunner.update(conn, sql, sysDept.getName(), sysDept.getLevel(), sysDept.getSeq(), sysDept.getRemark(), sysDept.getParent_id(), sysDept.getOperator(), sysDept.getOperate_time(), sysDept.getOperate_ip(), sysDept.getId());
        return count > 0 ? true : false;
    }

    public int batchUpdateLevel(Connection conn, List<SysDept> sysDeptList) throws Exception {
        String sql = "update sys_dept set level = ? where id = ?";

        final int len = sysDeptList.size();

        Object[][] param = new Object[len][2];

        for (int i = 0; i < len; i++) {
            param[i][0] = sysDeptList.get(i).getLevel();
            param[i][1] = sysDeptList.get(i).getId();
        }

        int count = queryRunner.batch(conn, sql, param).length;
        return count;
    }

}
